import xlwt
import os
import pymysql
import yaml
import time
import datetime


class Excel(object):
    def __init__(self, username=None):
        # 配置
        self.configs = self.get_config()
        self.conn = pymysql.connect(
            host=self.configs['mysql_config']['host'],
            port=self.configs['mysql_config']['port'],
            user=self.configs['mysql_config']['user'],
            password=self.configs['mysql_config']['password'],
            db=self.configs['mysql_config']['db'],
            charset=self.configs['mysql_config']['charset']
        )
        # 创建一个游标
        self.cursor = self.conn.cursor()
        # 存储数据的字典
        self.dict = {}
        # 账号
        self.users = ()
        # 指定的用户名
        self.username = username
        # 获取昨天时间戳
        self.today_time = int(time.mktime(
            datetime.date.today().timetuple()))
        self.yesterday_time = self.today_time - 86400
        # 表格
        self.myBook = None
        self.mySheet = None
        self.mySheet0 = None
        self.style = None
        # 当前行
        self.row = 0

    def get_config(self):
        '''
        获取config.yaml配置
        :return:
        '''
        # 获取当前文件的Realpath
        fileNamePath = os.path.split(os.path.realpath(__file__))[0]
        # 读取文件
        yamlPath = os.path.join(fileNamePath, 'config.yaml')
        # 加上 ,encoding='utf-8'，处理配置文件中含中文出现乱码的情况。
        file = open(yamlPath, 'r', encoding='utf-8')
        # 读取文件
        cont = file.read()
        # 返回配置
        return yaml.safe_load(cont)

    def pymysql_action(self, sql):
        # 创建一个游标
        # 执行 SQL 语句
        self.cursor.execute(sql)
        # 提交
        self.conn.commit()

    def get_users(self):
        '''
        获取用户
        :param username:当指定用户是，查询指定的用户
        '''
        if self.username is None:
            users_sql = "SELECT id,username,admin_name FROM `wjf_transaction_users` WHERE `deleted_at` is NULL and `status`=1"
        else:
            users_sql = "SELECT id,username,admin_name FROM `wjf_transaction_users` WHERE `deleted_at` is NULL  AND `status`=1 AND `username`="+self.username
        self.pymysql_action(users_sql)
        self.users = self.cursor.fetchall()
        return self.users

    def set_book(self, file_path):
        # 创建一个工作簿
        self.myBook = xlwt.Workbook()
        #  第一个工作簿
        self.position_print_excel()
        # 第二个工作簿
        self.print_excel()
        # 保存
        self.myBook.save(file_path)

    def position_set_excel(self):
        # 创建一个工作表格
        self.mySheet0 = self.myBook.add_sheet('内盘期货持仓统计表')
        # 字体
        font = xlwt.Font()
        font.bold = True
        # 设置单元格对齐方式
        alignment = xlwt.Alignment()
        # 0x01(左端对齐)、0x02(水平方向上居中对齐)、0x03(右端对齐)
        alignment.horz = 0x02
        # 0x00(上端对齐)、 0x01(垂直方向上居中对齐)、0x02(底端对齐)
        alignment.vert = 0x01
        # 设置自动换行
        alignment.wrap = 1
        # 初始化样式
        self.style = xlwt.XFStyle()
        self.style.font = font
        self.style.alignment = alignment
        self.row = 0

    def position_print_excel(self):
        self.users = self.get_users()
        if not bool(self.users):
            print("请添加期货中心账号")
            return False
        # 创建表格
        self.position_set_excel()
        for user in self.users:
            # 用户名称
            admin_name = user[2]
            # 每循环一个用户，做一个表头

            sql = "SELECT customer_rights,margin_occupation,available_funds,risk FROM `wjf_summary_datas` WHERE `deal_time_stamp` ="+str(
                self.today_time) + " AND `admin_name`='" + admin_name + "' AND `deleted_at` is NULL"
            self.pymysql_action(sql)
            summary = self.cursor.fetchone()
            customer_rights = 0
            margin_occupation = 0
            available_funds = 0
            risk = 0
            if summary is not None:
                customer_rights = summary[0]
                margin_occupation = summary[1]
                available_funds = summary[2]
                risk = summary[3]
            # 表名
            self.mySheet0.write_merge(
                self.row, self.row, 0, 15, '内盘期货持仓统计表（'+admin_name+'）', self.style)
            # 表头 当日总自唱  合并行1到2的列0到1。
            self.mySheet0.write_merge(
                self.row+1, self.row+2, 0, 3, '当日总资产（万）', self.style)
            self.mySheet0.write_merge(
                self.row+1, self.row+2, 4, 7, customer_rights)
            self.mySheet0.write_merge(
                self.row+1, self.row+2, 8, 11, '已用保证金（万）', self.style)
            self.mySheet0.write_merge(
                self.row+1, self.row+2, 12, 15, margin_occupation)
            self.mySheet0.write_merge(
                self.row+1, self.row+2, 16, 19, '可用保证金', self.style)
            self.mySheet0.write_merge(
                self.row+1, self.row+2, 20, 23, available_funds)
            self.mySheet0.write_merge(
                self.row+1, self.row+2, 24, 27, '风险度', self.style)
            self.mySheet0.write_merge(
                self.row+1, self.row+2, 28, 31, risk)

            self.mySheet0.write_merge(
                self.row+3, self.row+4, 0, 27, '多仓', self.style)

            self.mySheet0.write_merge(
                self.row+5, self.row+6, 0, 1, '品种', self.style)
            self.mySheet0.write_merge(
                self.row+5, self.row+6, 6, 7, '手数', self.style)
            self.mySheet0.write_merge(
                self.row+5, self.row+6, 8, 11, '均价（元）', self.style)
            self.mySheet0.write_merge(
                self.row+5, self.row+6, 12, 15, '当日盈亏(万)', self.style)
            self.mySheet0.write_merge(
                self.row+5, self.row+6, 16, 19, '5日累计盈亏(万）', self.style)
            self.mySheet0.write_merge(
                self.row+5, self.row+6, 20, 23, '10日累计盈亏（万）', self.style)
            self.mySheet0.write_merge(
                self.row+5, self.row+6, 24, 27, '20日累计盈亏（万）', self.style)
            self.mySheet0.write_merge(
                self.row+5, self.row+6, 28, 31, '累计持仓盈亏(万', self.style)
            self.row += 6
            self.get_position_user_datas(admin_name)

    def get_position_user_datas(self, admin_name):
        '''
        获取单个用户的数据
        '''
        # 持仓手数  每个品种买持仓合计
        # 均价    成交价的平均值
        # 当日盈亏
        sql = "SELECT `contract`,`exchange_code`,`exchange_num`,SUM(`num`) as sum_num, AVG(`final_price`) as avg_final_price FROM `wjf_deal_datas` WHERE `deal_time_stamp` ="+str(
            self.today_time) + " AND `admin_name`='" + admin_name + "' AND `deleted_at` is NULL AND `buy_sell`='买' GROUP BY `contract`,`deal_time_stamp`"
        self.pymysql_action(sql)
        self.dict = self.cursor.fetchall()
        if not bool(self.dict):
            print("该账号下没有数据")
            return False
        i = 0
        for dict_one in self.dict:
            i += 1
            # 品种
            self.mySheet0.write_merge(
                self.row+i,  self.row+i, 0, 0, dict_one[1])
            self.mySheet0.write_merge(
                self.row+i,  self.row+i, 1, 1, dict_one[0])
            # 手数
            self.mySheet0.write_merge(
                self.row+i,  self.row+i, 6, 7, dict_one[3])
            # 均价
            self.mySheet0.write_merge(
                self.row+i,  self.row+i, 8, 11, dict_one[4])
            # 当日盈亏
            sql = "SELECT SUM(`profit_loss`) as sum_profit_loss FROM `wjf_position_datas` WHERE `deal_time_stamp` >="+str(
                self.today_time) + " AND `admin_name`='" + admin_name + "' AND `deleted_at` is NULL AND `contract`='" + dict_one[0]+"'"
            self.pymysql_action(sql)
            profit_loss_one = self.cursor.fetchone()
            # 浮动盈亏
            profit_loss = 0
            if profit_loss_one is not None:
                profit_loss = profit_loss_one[0]
            self.mySheet0.write_merge(
                self.row+i,  self.row+i, 12, 15, profit_loss)
            # 5日累计盈亏(万）
            old_time = self.today_time - 86400*5
            sql = "SELECT SUM(`profit_loss`) as sum_profit_loss FROM `wjf_position_datas` WHERE `deal_time_stamp` >="+str(
                old_time) + " AND `admin_name`='" + admin_name + "' AND `deleted_at` is NULL AND `contract`='" + dict_one[0]+"'"
            self.pymysql_action(sql)
            profit_loss_one = self.cursor.fetchone()
            # 浮动盈亏
            profit_loss = 0
            if profit_loss_one is not None:
                profit_loss = profit_loss_one[0]
            self.mySheet0.write_merge(
                self.row+i,  self.row+i, 16, 19, profit_loss)
            # 10日累计盈亏（万）
            old_time = self.today_time - 86400*10
            sql = "SELECT SUM(`profit_loss`) as sum_profit_loss FROM `wjf_position_datas` WHERE `deal_time_stamp` >="+str(
                old_time) + " AND `admin_name`='" + admin_name + "' AND `deleted_at` is NULL AND `contract`='" + dict_one[0]+"'"
            self.pymysql_action(sql)
            profit_loss_one = self.cursor.fetchone()
            # 浮动盈亏
            profit_loss = 0
            if profit_loss_one is not None:
                profit_loss = profit_loss_one[0]
            self.mySheet0.write_merge(
                self.row+i,  self.row+i, 20, 23, profit_loss)
            # 20日累计盈亏（万）
            old_time = self.today_time - 86400*20
            sql = "SELECT SUM(`profit_loss`) as sum_profit_loss FROM `wjf_position_datas` WHERE `deal_time_stamp` >="+str(
                old_time) + " AND `admin_name`='" + admin_name + "' AND `deleted_at` is NULL AND `contract`='" + dict_one[0]+"'"
            self.pymysql_action(sql)
            profit_loss_one = self.cursor.fetchone()
            # 浮动盈亏
            profit_loss = 0
            if profit_loss_one is not None:
                profit_loss = profit_loss_one[0]
            self.mySheet0.write_merge(
                self.row+i,  self.row+i, 24, 27, profit_loss)
            # 累计持仓盈亏(万)
            sql = "SELECT SUM(`profit_loss`) as sum_profit_loss FROM `wjf_position_datas` WHERE `admin_name`='" + \
                admin_name + "' AND `deleted_at` is NULL AND `contract`='" + \
                dict_one[0]+"'"
            self.pymysql_action(sql)
            profit_loss_one = self.cursor.fetchone()
            # 浮动盈亏
            profit_loss = 0
            if profit_loss_one is not None:
                profit_loss = profit_loss_one[0]
            self.mySheet0.write_merge(
                self.row+i,  self.row+i, 28, 31, profit_loss)
        self.row = self.row+len(self.dict)

    def get_user_datas(self, admin_name):
        '''
        获取单个用户的数据
        '''
        sql = "SELECT `contract`,`exchange_code`,`exchange_num`,deal_time,MIN(`num`) as min_num,SUM(`num`) as sum_num, AVG(`final_price`) as avg_final_price,SUM(`profit_loss`) as sum_profit_loss FROM `wjf_deal_datas` WHERE `deal_time_stamp` ="+str(
            self.today_time) + " AND `admin_name`='" + admin_name + "' AND `deleted_at` is NULL GROUP BY `contract`,`deal_time_stamp`"
        self.pymysql_action(sql)
        self.dict = self.cursor.fetchall()
        if not bool(self.dict):
            print("该账号下没有数据")
            return False
        i = 0
        for dict_one in self.dict:
            i += 1
            # 品种
            self.mySheet.write_merge(
                self.row+i,  self.row+i, 0, 0, dict_one[1])
            self.mySheet.write_merge(
                self.row+i,  self.row+i, 1, 1, dict_one[0])
            # 平仓时间
            self.mySheet.write_merge(
                self.row+i,  self.row+i, 2, 3, dict_one[3].strftime('%Y-%m-%d %H:%M:%S'))
            self.mySheet.write_merge(
                self.row+i,  self.row+i, 4, 5, dict_one[4])
            self.mySheet.write_merge(
                self.row+i,  self.row+i, 6, 7, 0)
            self.mySheet.write_merge(
                self.row+i,  self.row+i, 8, 8, dict_one[5])
            self.mySheet.write_merge(
                self.row+i,  self.row+i, 9, 10, dict_one[6])
            self.mySheet.write_merge(
                self.row+i,  self.row+i, 11, 13, dict_one[7])
            # 历史盈亏 取前一天的该品种的平仓盈亏
            yesterday_profit_loss_sql = "SELECT SUM(`profit_loss`) as sum_profit_loss FROM `wjf_position_datas` WHERE `deal_time_stamp` ="+str(
                self.yesterday_time) + " AND `admin_name`='" + admin_name + "' AND `deleted_at` is NULL AND `contract`='" + dict_one[0]+"'"
            self.pymysql_action(yesterday_profit_loss_sql)
            yesterday_profit_loss_one = self.cursor.fetchone()
            yesterday_profit_loss = 0
            if yesterday_profit_loss_one is not None:
                yesterday_profit_loss = yesterday_profit_loss_one[0]
            self.mySheet.write_merge(
                self.row+i,  self.row+i, 14, 16, yesterday_profit_loss)
            # 持仓盈亏 持仓盈亏=历史持仓盈亏+当日开仓持仓盈亏
            self.mySheet.write_merge(
                self.row+i,  self.row+i, 17, 18, 0)
            # 总盈亏
            self.mySheet.write_merge(
                self.row+i,  self.row+i, 19, 21, 0)
            self.mySheet.write_merge(
                self.row+i,  self.row+i, 22, 24, '暂无数据')
        self.row = self.row+len(self.dict)
        # return self.dict

    def set_excel(self):
        '''
        创建表格
        '''
        # 创建一个工作表格
        self.mySheet = self.myBook.add_sheet('内盘期货平仓统计表')
        # 字体
        font = xlwt.Font()
        font.bold = True
        # 设置单元格对齐方式
        alignment = xlwt.Alignment()
        # 0x01(左端对齐)、0x02(水平方向上居中对齐)、0x03(右端对齐)
        alignment.horz = 0x02
        # 0x00(上端对齐)、 0x01(垂直方向上居中对齐)、0x02(底端对齐)
        alignment.vert = 0x01
        # 设置自动换行
        alignment.wrap = 1
        # 初始化样式
        self.style = xlwt.XFStyle()
        self.style.font = font
        self.style.alignment = alignment
        self.row = 0

    def print_excel(self):
        self.users = self.get_users()
        if not bool(self.users):
            print("请添加期货中心账号")
            return False
        # 创建表格
        self.set_excel()
        for user in self.users:
            # 用户名称
            admin_name = user[2]
            # 每循环一个用户，做一个表头
            # 表名
            self.mySheet.write_merge(
                self.row, self.row, 0, 15, '内盘期货平仓统计表（'+admin_name+'）', self.style)
            self.mySheet.write_merge(
                self.row, self.row, 16, 24, '时间：（今天）', self.style)
            # 表头 品种  合并行1到2的列0到1。
            self.mySheet.write_merge(
                self.row+1, self.row+2, 0, 1, '品种', self.style)
            self.mySheet.write_merge(
                self.row+1, self.row+2, 2, 3, '平仓日期', self.style)
            self.mySheet.write_merge(
                self.row+1, self.row+2, 4, 5, '最小手数', self.style)
            self.mySheet.write_merge(
                self.row+1, self.row+2, 6, 7, '倍数', self.style)
            self.mySheet.write_merge(
                self.row+1, self.row+2, 8, 8, '手数', self.style)
            self.mySheet.write_merge(
                self.row+1, self.row+2, 9, 10, '购买均价（元）', self.style)
            self.mySheet.write_merge(
                self.row+1, self.row+1, 11, 16, '全部平仓盈亏', self.style)
            self.mySheet.write_merge(
                self.row+2, self.row+2, 11, 13, '当日盈亏(万)', self.style)
            self.mySheet.write_merge(
                self.row+2, self.row+2, 14, 16, '历史盈亏(万)', self.style)
            self.mySheet.write_merge(
                self.row+1, self.row+2, 17, 18, '持仓盈亏（万）', self.style)
            self.mySheet.write_merge(
                self.row+1, self.row+2, 19, 21, '总盈亏（万）', self.style)
            self.mySheet.write_merge(
                self.row+1, self.row+2, 22, 24, '平仓简述', self.style)
            self.row += 2
            # 拿每个用户名去查他昨天的数据
            self.get_user_datas(admin_name)

    def test(self):
        '''
        测试
        '''
        self.users = self.get_users()
        if not bool(self.users):
            print("请添加期货中心账号")
            return False
        # 创建表格
        self.set_excel()
        for user in self.users:
            # 用户名称
            admin_name = user[2]
            # 拿每个用户名去查他昨天的数据
            self.get_user_datas(admin_name)

    def __del__(self):
        # 关闭游标
        self.cursor.close()
        # 关闭数据库连接
        self.conn.close()


if __name__ == '__main__':
    file_path = '内盘期货统计表.xls'
    if os.path.exists(file_path) is True:
        try:
            os.remove(file_path)
        except Exception as e:
            print(file_path+'已存在，可能被占用，不能写入')
            quit()
    obj = Excel()
    obj.set_book(file_path)
    # obj.test()
